﻿using Model;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace DataAccess
{
    public class PositionDA: DBConnection
    {
        public PositionDA()
        {
            sqlCommand.Connection = sqlConnection;
        }
        
        /// <summary>
        /// Add a new Position to database
        /// </summary>
        /// <param name="Position">The new Position to add </param>
        /// <returns>true if adding successfully, false otherwise</returns>
        public int addPosition(string name)
        {
            string query = "insert into " + Common.CHUC_VU +
                " values(@positonname); select SCOPE_IDENTITY();";
            sqlCommand.CommandText = query;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@positonname", name);
            try
            {
                open();
                int idRe = -1;
                sqlCommand.CommandText = query;
                string re = sqlCommand.ExecuteScalar().ToString();
                idRe = Int32.Parse(re);
                close();
                return idRe;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
                return -1;
            }

        }

        public bool updatePosition(int id, string name)
        {
            //log here
            string query = "update " + Common.BENH_NHAN +
                " set positionname = @positionname, " +
                "where positionid = " + id;

            sqlCommand.Parameters.Clear();
            sqlCommand.Parameters.AddWithValue("@positionname", name);
            

            sqlCommand.CommandText = query;

            try
            {
                open();
                sqlCommand.ExecuteNonQuery();
                close();
                return true;
            }
            catch (SqlException s)
            {
                //log here
                Console.WriteLine(s.Message);
                return false;
            }
        }

        public Position getPositionById(int id)
        {
            string query = "select * from " + Common.CHUC_VU + " where positionid=" + id;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                Position Position = new Position();
                while (sqlDataReader.Read())
                {
                    Position = createPositionFromReader(sqlDataReader);

                }
                close();
                return Position;
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }


        }

        public Position[] getAllPosition()
        {
            string query = "select * from " + Common.CHUC_VU;
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return null;
                }
                List<Position> liPosition = new List<Position>();
                Position position  = new Position();
                while (sqlDataReader.Read())
                {
                    position = createPositionFromReader(sqlDataReader);
                    liPosition.Add(position);
                }
                close();
                return liPosition.ToArray();
            }
            catch
            {
                return null;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }

        }

        public bool isPositionExist(string name)
        {
            string query = "select positionname from " + Common.CHUC_VU +
                " where positionname = N'" + name + "'";
            sqlCommand.CommandText = query;
            try
            {
                open();
                sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader == null)
                {
                    //log here
                    return false;
                }
                while (sqlDataReader.Read())
                {
                    close();
                    return true;

                }
                return false;
            }
            catch
            {
                return false;
            }
            finally
            {
                if (sqlDataReader != null)
                    sqlDataReader.Close();
                close();
            }
        }
        

        private Position createPositionFromReader(SqlDataReader rd)
        {

            if (rd != null && !rd.IsClosed)
            {
                Position Position = new Position();
                Position.setPositionId(rd.GetInt32(0));
                Position.setPositionName(rd.GetString(1));
                return Position;
            }
            else
            {
                return null;
            }

        }
    }
}
